/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50144
 Source Host           : localhost
 Source Database       : p

 Target Server Type    : MySQL
 Target Server Version : 50144
 File Encoding         : utf-8

 Date: 04/16/2012 08:49:32 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `allocation_items`
-- ----------------------------
DROP TABLE IF EXISTS `allocation_items`;
CREATE TABLE `allocation_items` (
  `code` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `allocation_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT '1' COMMENT '1',
  `remark` text CHARACTER SET utf8,
  `return_date` datetime DEFAULT NULL,
  `return` int(11) DEFAULT NULL COMMENT '0',
  `category_id` int(11) NOT NULL,
  `allocation_item_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`allocation_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `allocation_items`
-- ----------------------------
BEGIN;
INSERT INTO `allocation_items` VALUES (null, '12', '1', '', null, null, '6', '16'), (null, '13', '1', 'macbook pro', null, null, '20', '17');
COMMIT;

-- ----------------------------
--  Table structure for `allocations`
-- ----------------------------
DROP TABLE IF EXISTS `allocations`;
CREATE TABLE `allocations` (
  `allocation_id` int(11) NOT NULL AUTO_INCREMENT,
  `allocation_date` date DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `detail` text CHARACTER SET utf8,
  `return_date` date DEFAULT NULL,
  `allocation_type` varchar(255) DEFAULT NULL,
  `place_id` int(11) NOT NULL,
  PRIMARY KEY (`allocation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `allocations`
-- ----------------------------
BEGIN;
INSERT INTO `allocations` VALUES ('12', '2012-04-07', '9', 'ขอใช้ ปากา เคมี 1 แทงครับ', null, null, '0'), ('13', '2012-04-07', '1', 'ใช้ส่วนตัว', null, null, '0');
COMMIT;

-- ----------------------------
--  Table structure for `budgets`
-- ----------------------------
DROP TABLE IF EXISTS `budgets`;
CREATE TABLE `budgets` (
  `budget_id` int(11) NOT NULL AUTO_INCREMENT,
  `budget_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ชืองบประมาณ สำหรับการจัดซื้อ',
  `year` varchar(255) DEFAULT NULL,
  `remark` text,
  PRIMARY KEY (`budget_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `budgets`
-- ----------------------------
BEGIN;
INSERT INTO `budgets` VALUES ('1', 'งบประมาณประจำปี', '2554', null), ('2', 'งบประมาณประจำปี', '2555', null);
COMMIT;

-- ----------------------------
--  Table structure for `company`
-- ----------------------------
DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` (
  `company_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `address` text CHARACTER SET utf8,
  `tel` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `fax` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `company`
-- ----------------------------
BEGIN;
INSERT INTO `company` VALUES ('1', 'Cyberland Pacific Co.,Ltd.', '40/245 หมู่ 7 ถ.พระยาสุเรนทร์ แขวงสามวาตะวันตก เขตคลองสามวา กรุงเทพมหานคร 10510', '0-2914-2942', '0-2908-0945', 'patchaya@cyberland.co.th'), ('2', 'ร้านมาสเตอร์ สาขา ตรงข้ามเซ็นทรัลลาดพร้าว', '206/1 ถนนพหลโยธิน แขวงลาดยาว เขตจตุจักร กรุงเทพมหานคร 10900\nhttp://www.masterphotonetwork.com', '085-825-7510', '0-2930-9389', ''), ('3', 'work progression co.ltd', '199/22 หมู่ 6 แขวงหลักสอง เขตบางแค กทม.10160\nhttp://workprogression.com', '02-8080437', '02-8080437', ''), ('4', 'แกรนด์ฟิสค์ จำกัด', '13,15 ซ.อ่อนนุช 70/1 ถ.อ่อนนุช แขวงประเวศ เขตประเวศ กรุงเทพมหานคร 10250', '02-3202242-9', '02-3202242-9', 'grandfisc@hotmail.com');
COMMIT;

-- ----------------------------
--  Table structure for `controller_groups`
-- ----------------------------
DROP TABLE IF EXISTS `controller_groups`;
CREATE TABLE `controller_groups` (
  `c_group_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_name` varchar(255) NOT NULL,
  `sort_order` int(11) NOT NULL,
  PRIMARY KEY (`c_group_id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `controller_groups`
-- ----------------------------
BEGIN;
INSERT INTO `controller_groups` VALUES ('1', 'จัดการผู้ใช้งาน', '4'), ('2', 'จัดการข้อมูลระบบ', '3'), ('3', 'ข้อมูลสรุป&รายงาน', '2'), ('4', 'ลงทะเบียนใหม่', '1'), ('5', 'ข้อมูลส่วนตัว', '5'), ('6', 'ค้นหา', '0'), ('7', 'งานซ่อม', '4'), ('8', 'ตรวจสถาการใช้งาน', '6'), ('9', 'สำรวจความต้องการ', '4'), ('10', 'Developer', '7'), ('11', 'เบิกจ่ายวัสดุครุภัณฑ์', '2');
COMMIT;

-- ----------------------------
--  Table structure for `controllers`
-- ----------------------------
DROP TABLE IF EXISTS `controllers`;
CREATE TABLE `controllers` (
  `c_id` int(255) NOT NULL AUTO_INCREMENT,
  `controller` varchar(255) NOT NULL,
  `detail` varchar(255) NOT NULL,
  `sort_order` varchar(255) NOT NULL,
  `c_group_id` int(255) NOT NULL,
  `is_menu` varchar(255) NOT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=MyISAM AUTO_INCREMENT=89 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `controllers`
-- ----------------------------
BEGIN;
INSERT INTO `controllers` VALUES ('1', 'admin/users', 'รายการผู้ใช้งาน', '', '1', '1'), ('2', 'admin/user_add', 'เพิ่มผู้ใช้งานใหม่', '', '1', '1'), ('3', 'admin/user_edit', 'แก้ไข้ผู้ใช้งาน', '', '1', '0'), ('4', 'admin/pass_edit', 'เปลี่ยนรหัสผู้ใช้งาน', '', '1', '0'), ('5', 'admin/user_roles', 'รายการกลุ่มผู้ใช้งาน', '', '1', '1'), ('6', 'admin/user_role_add', 'เพิ่มกลุ่มผู้ใช้งานใหม่', '', '1', '0'), ('7', 'admin/role_edit', 'แก้ไข้กลุ่มผู้ใช้งาน', '', '1', '0'), ('8', 'admin/user_role_delete', 'ลบกลุ่มผู้ใช้งาน', '', '1', '0'), ('29', 'admin/material_add', 'ลงทะเบียนครุภัณฑ์ใหม่', '', '4', '1'), ('30', 'admin/material_add2', 'ลงทะเบียนวัสดุใหม่', '', '4', '1'), ('31', 'admin/categories', 'หมวดวัสดุ/ครุภัณฑ์', '', '2', '1'), ('32', 'admin/category_add', 'เพิ่มหมวด วัสดุ/ครุภัณฑ์ ใหม่', '', '2', '0'), ('33', 'admin/category_edit', 'แก้ไขหมวด วัสดุ/ครุภัณฑ์', '', '2', '0'), ('34', 'admin/company', 'ข้อมูลบริษัท', '', '2', '1'), ('35', 'admin/company_add', 'เพิ่มข้อมูลบริษัท ใหม่', '', '2', '0'), ('36', 'admin/company_edit', 'แก้ไขข้อมูลบริษัท', '', '2', '0'), ('37', 'admin/room', 'ข้อมูลห้อง/สถานที่', '', '2', '1'), ('38', 'admin/room_add', 'เพิ่มข้อมูลห้อง/สถานที่', '', '2', '0'), ('39', 'admin/room_edit', 'แก้ไขข้อมูลห้อง/สถานที่', '', '2', '0'), ('40', 'admin/room_delete', 'ลบ ข้อมูลห้อง/สถานที่', '', '2', '0'), ('41', 'admin/pass_edit2', 'แก้ไขรหัสผ่าน', '', '5', '0'), ('42', 'admin/user_edit2', 'แก้ไขข้อมูลส่วนตัว', '', '5', '0'), ('44', 'admin/maintains', 'ข้อมูล ชนิดงานซ่อม', '', '2', '1'), ('45', 'admin/maintain_add', 'main tain type add', '', '2', '0'), ('46', 'admin/maintain_edit', 'แก้ไขชนิดงาน ซ่อม', '', '2', '0'), ('47', 'report/material2_by_year', 'รายงาน ครุภัณฑ์ประจำปีการศึกษา', '', '3', '1'), ('48', 'report/material1_by_year', 'รายงาน วัสดุประจำปีการศึกษา', '', '3', '1'), ('49', 'admin/search', 'รายงาน วัสดุ-ครุภัณฑ์', '', '3', '1'), ('51', 'report/page1', 'รายงานงานวัสดุ/ครุภัณฑ์หมดอายุการใช้งาน', '', '3', '1'), ('52', 'report/page5', 'รายงานความต้องการใช้ ครุภัณฑ์ในห้องปฏิบัติการ', '', '3', '0'), ('53', 'report/m_request_by_year1', 'รายงานความต้องการใช้วัสดุประจำภาคการศึกษา ', '', '3', '1'), ('54', 'report/m_request_by_year2', 'รายงานความต้องการใช้ครุภัณฑ์ประจำภาคการศึกษา ', '', '3', '1'), ('55', 'report/summary2', 'รายงานข้อมูลทะเบียนครุภัณฑ์ ', '', '3', '0'), ('56', 'report/summary', 'รายงานข้อมูลทะเบียน วัสดุ-ครุภัณฑ์ ', '', '3', '1'), ('57', 'report/budget2', 'รายงานการครุภัณฑ์ประจำงบประมาณ', '', '3', '1'), ('58', 'report/budget1', 'รายงานการวัสดุประจำงบประมาณ', '', '3', '1'), ('59', 'report/page9', 'รายงานสภาพการใช้งานครุภัณฑ์ ', '', '3', '0'), ('60', 'report/page10', 'รายงานการซ่อมบำรุงครุภัณฑ์', '', '3', '0'), ('61', 'report/maintain_company', 'รายงานการส่งซ่อมครุภัณฑ์', '', '3', '0'), ('62', 'report/page12', 'รายงานการจำหน่ายครุภัณฑ์หมดอายุการใช้งาน ', '', '3', '1'), ('64', 'admin/budgets', 'ข้อมูลงบประมาณ', '', '2', '1'), ('65', 'admin/material_view', 'รายละอียดวัสดุ/ครุภัฑฑ์', '', '2', '0'), ('66', 'admin/maintain_request', 'แจ้งซ้อมใหม่', '', '7', '1'), ('67', 'admin/maintain_request_list', 'รายการงานแจ้งซ่อม', '', '7', '1'), ('68', 'admin/maintain_request_process_list', 'รายการงานส่งซ่อม', '', '7', '0'), ('69', 'admin/maintain_request_finish_list', 'รายการงานซ่อมเสร็จแล้ว', '', '7', '0'), ('70', 'admin/maintain_detail', 'รายละเอียดงานซ่อม', '', '7', '0'), ('72', 'admin/m_maintain_add', 'บันทึกงานซ่อม', '', '7', ''), ('73', 'admin/maintain_finish', 'ปิดงานซ่อม', '', '7', ''), ('74', 'report/query_form', 'Query Builders', '', '10', '1'), ('75', 'admin/materail_edit', 'แก้ไข้วัสดุ', '', '2', ''), ('76', 'report/all_report', 'Query list', '', '10', '1'), ('77', 'admin/maintain_view', 'รายละเอียดงานซ่อม', '', '7', '0'), ('78', 'admin/maintain_request_delete', 'ลบใบแจ้งซ่อม', '', '7', ''), ('79', 'admin/m_request_add', 'ฟอร์มกรอกสำรวจความต้องการใช้งาน', '', '9', '1'), ('80', 'admin/m_request_list', 'ข้อมูลสำรวจความต้องการของคุณ', '', '9', '1'), ('81', 'admin/role_delete', 'ลบกลุ่มผุ้ใช้งาน', '', '0', ''), ('82', 'admin/material_edit2', 'แก้ไขวัสดุ', '', '0', '1'), ('83', 'admin/material_add_item\r\nmaterial_add_item', 'เพิ่มรายการวัสดุครุภัณฑ์', '', '7', '0'), ('84', 'admin/inventory_list', 'รายกาวัสดุ-ครุภัณฑ์ คงเหลือ', '', '11', '1'), ('85', 'admin/teacher_request', 'ขอเบิกวัสดุ-ครุภัณฑ์', '', '11', '1'), ('86', 'admin/request_list', 'รายการขอเบิก วัสดุ-ครุภัณฑ์', '', '11', '1'), ('87', 'admin/request_detail', 'รายละเอียดขอเบิก วัสดุ-ครุภัณฑ์', '', '11', ''), ('88', 'report/dashboard', 'สรุปข้อมูลในระบบ', '', '0', '');
COMMIT;

-- ----------------------------
--  Table structure for `maintain_status`
-- ----------------------------
DROP TABLE IF EXISTS `maintain_status`;
CREATE TABLE `maintain_status` (
  `status_id` int(11) NOT NULL,
  `status_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `maintain_status`
-- ----------------------------
BEGIN;
INSERT INTO `maintain_status` VALUES ('1', 'แจ้งซ่อใหม่'), ('2', 'กำลังดำเนินการ'), ('3', 'ซ่อมเสร็จแล้ว'), ('4', 'ซ่อมไม่ได้แล้วชำรุด');
COMMIT;

-- ----------------------------
--  Table structure for `maintains`
-- ----------------------------
DROP TABLE IF EXISTS `maintains`;
CREATE TABLE `maintains` (
  `maintain_id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) CHARACTER SET utf8 NOT NULL,
  `user_id` int(11) NOT NULL,
  `subject` varchar(255) CHARACTER SET utf8 NOT NULL,
  `remark` text CHARACTER SET utf8 NOT NULL,
  `status_id` int(11) NOT NULL,
  `create_date` datetime NOT NULL,
  `finish_date` date NOT NULL,
  `price` varchar(255) NOT NULL,
  `remark2` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `warranty` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`maintain_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `maintains`
-- ----------------------------
BEGIN;
INSERT INTO `maintains` VALUES ('1', 'บส102/2555', '9', 'คอมพิวเตอร์เปิดไม่ติด', 'ทดสอบ', '4', '2012-04-10 12:51:52', '2012-04-13', '1000', '', '1 เดือน'), ('2', 'บส103/2555', '9', 'คอมพิวเตอร์เปิดไม่ติด', 'เปิอเครื่องแล้วดับเองตลอดเลย', '1', '2012-04-15 13:20:07', '0000-00-00', '', '', ''), ('3', 'บส103/2555', '9', 'xxx', 'ปปป', '1', '2012-04-15 13:25:44', '0000-00-00', '', '', '');
COMMIT;

-- ----------------------------
--  Table structure for `material_categories`
-- ----------------------------
DROP TABLE IF EXISTS `material_categories`;
CREATE TABLE `material_categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `cat_base_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `material_categories`
-- ----------------------------
BEGIN;
INSERT INTO `material_categories` VALUES ('2', 'คอมพิวเตอร์ตั้งโต้ะ', '2'), ('3', 'โต้ะคอมพิวเตอร์', '5'), ('4', 'เครื่อง printer', '2'), ('5', 'ตู้เก็บของทั่วไป', '5'), ('6', 'เครื่องเขียน', '3'), ('7', 'เครื่องปรับอากาศ', '1'), ('8', 'อุปกรณ์สำหรับทำความสะอาด', '6'), ('9', 'อุปกรณ์ขยายเสียง/ลำโพง', '1'), ('10', 'หนังสือเรียน', '4'), ('11', 'เครื่องถ่ายเอกสาร', '1'), ('14', 'กระดาษ A4', '3'), ('16', 'ลำโพง', '2'), ('17', 'เทปกาว', '3'), ('18', 'ตู้เย็น', '1'), ('19', 'พัดลม', '1'), ('20', 'Notebook', '2'), ('21', 'Tablate', '2'), ('22', 'หน้าจอคอมพิวเตอร์', '2'), ('23', 'Keybord  คอมพิวเตอร์', '2'), ('24', 'กระดาษ A5', '3');
COMMIT;

-- ----------------------------
--  Table structure for `material_items`
-- ----------------------------
DROP TABLE IF EXISTS `material_items`;
CREATE TABLE `material_items` (
  `code` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `register_date` datetime DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `material_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT '1',
  `barcode` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `material_items`
-- ----------------------------
BEGIN;
INSERT INTO `material_items` VALUES ('10001', '2012-03-21 14:56:47', '', '39', '10', ''), ('10007', '2012-04-06 15:03:48', '', '42', '10', ''), ('1200006', '2012-04-07 12:41:10', 'สีดำ', '44', '2', '1000001200007'), ('1200014', '2012-04-07 12:43:21', 'สีแดง', '44', '1', '1000001200015'), ('บส102/2555', '2012-04-05 15:49:55', '', '40', '1', ''), ('บส103/2555', '2012-04-05 15:50:04', '', '40', '1', ''), ('บส104/2555', '2012-04-05 15:50:17', '', '40', '1', ''), ('บส105/2555', '2012-04-05 15:51:06', '', '40', '1', ''), ('บส106/2555', '2012-04-05 15:51:32', '', '40', '1', ''), ('บส109/2555', '2012-03-21 13:39:50', '', '38', '1', ''), ('บส110/2555', '2012-04-06 09:53:44', '', '41', '1', ''), ('บส112/2555', '2012-04-06 09:53:54', '', '41', '1', ''), ('บส113/2555', '2012-04-06 09:54:08', '', '41', '1', ''), ('บส114/2555', '2012-04-06 09:54:24', '', '41', '1', '');
COMMIT;

-- ----------------------------
--  Table structure for `material_registers`
-- ----------------------------
DROP TABLE IF EXISTS `material_registers`;
CREATE TABLE `material_registers` (
  `material_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'รหัส วัสดุครุภัณฑ์',
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ชื่อวัสดุครภัณฑ์',
  `model` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL COMMENT 'หมวด',
  `type_id` int(11) DEFAULT NULL COMMENT 'เป็น วัสดุ หรือ ครุภัณฑ์ มี ค่า 1 = วัสดุ  2 = ครุถัณฑ์',
  `buy_date` date DEFAULT NULL,
  `buy_price` varchar(255) DEFAULT NULL,
  `brand` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `warranty` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'เงือนไขการรับประกัน',
  `detail` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `responsible` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ผู้รับผิดชอบ',
  `thumbnail` varchar(255) DEFAULT NULL COMMENT 'เก็บรูปที่ 1',
  `create_date` varchar(255) DEFAULT NULL COMMENT 'วันที่กรอก ข้อมูล',
  `last_modify` date DEFAULT NULL COMMENT 'วันที่แก้ไข้ข้อมูลล่าสุด',
  `year` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ปีการ ศึกษา',
  `budget_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`material_id`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `material_registers`
-- ----------------------------
BEGIN;
INSERT INTO `material_registers` VALUES ('38', 'เครื่องคอมพิวเตอร์ตั้งโต้ะ HP', 'RP5700', '2', '2', '0000-00-00', '20000', 'HP', '1 ปี', 'HP announced the greenest computer ever – the RP5700, and was awarded the first “gold” rating by EPEAT. How does HP make it to gold? First, by redesigning for end of life – 95% of components are recyclable, and the internal chassis can be taken apart by h', null, '2fc58f7741196a8a2b92a18b904e0ae0.jpg', '12/03/19 15:01:46', '2012-03-19', null, '2', '1'), ('39', 'กระดาษ A 4 Duble A', 'a4', '12', '2', '2012-03-21', '120', 'Duble A', '-', '', null, 'd656fff3709b991fcabe69dc55c86f58.jpg', '12/03/21 13:52:00', '2012-03-21', null, '2', '2'), ('40', 'คอมพิวเตอร์  Notebook Lenovo think pad', 'x200', '2', '2', '2012-04-05', '31000', 'lenovo', '3 ปี', '', null, '', '12/04/05 15:49:13', '2012-04-05', null, '2', '1'), ('41', 'Macbook pro', 'mac pro', '2', '2', '2012-04-06', '39000', 'apple', '1 ปี', '', null, 'd52dd4d9c123a003d4e6d875036a8e09.jpg', '12/04/06 09:53:18', '2012-04-06', null, '1', '2'), ('42', 'Double A', 'a4', '14', '2', '0000-00-00', '120', 'Double A', '', 'xxxx', null, 'c604d84046ed3a18ff937ac2e06eb523.jpg', '12/04/06 14:54:46', '2012-04-06', null, '2', '4'), ('43', 'กระดาษ A4', 'A4', '14', '2', '2012-04-07', '120', 'Double A', '', 'กระดาษ A 4 400 แผ่นต่อ 1 รีมชนิด หนา', null, '4a171041eea3cc397ba5220c87edcdfc.jpg', '12/04/07 11:47:47', '2012-04-07', null, '2', '2'), ('44', 'ปากกาเคมีสีสำหรับเขียนกระดาษ', '-', '6', '1', '2012-04-07', '15', 'ตราม้า', '', 'สีดำ', null, 'dad96f4ecbe451dba6ec62515a2ea36c.gif', '12/04/07 11:54:56', '2012-04-07', null, '2', '2');
COMMIT;

-- ----------------------------
--  Table structure for `material_request_items`
-- ----------------------------
DROP TABLE IF EXISTS `material_request_items`;
CREATE TABLE `material_request_items` (
  `mr_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `mr_id` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`mr_item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `material_request_items`
-- ----------------------------
BEGIN;
INSERT INTO `material_request_items` VALUES ('22', '16', '2', '2');
COMMIT;

-- ----------------------------
--  Table structure for `material_requests`
-- ----------------------------
DROP TABLE IF EXISTS `material_requests`;
CREATE TABLE `material_requests` (
  `mr_id` int(255) NOT NULL AUTO_INCREMENT,
  `detail` text NOT NULL,
  `place_id` int(255) NOT NULL,
  `user_id` int(255) NOT NULL,
  `create_date` date NOT NULL,
  PRIMARY KEY (`mr_id`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `material_requests`
-- ----------------------------
BEGIN;
INSERT INTO `material_requests` VALUES ('15', 'xxxxx', '3', '9', '2012-04-15'), ('16', '', '1', '9', '2012-04-15');
COMMIT;

-- ----------------------------
--  Table structure for `material_types`
-- ----------------------------
DROP TABLE IF EXISTS `material_types`;
CREATE TABLE `material_types` (
  `type_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ชือ ชนิด material มี 2 อัน วัสดุ กับ ครุภัณฑ์',
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `material_types`
-- ----------------------------
BEGIN;
INSERT INTO `material_types` VALUES ('1', 'วัสดุ'), ('2', 'ครุภัณฑ์');
COMMIT;

-- ----------------------------
--  Table structure for `meterial_base_categories`
-- ----------------------------
DROP TABLE IF EXISTS `meterial_base_categories`;
CREATE TABLE `meterial_base_categories` (
  `cat_base_id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_base_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `sort_order` int(11) DEFAULT NULL,
  PRIMARY KEY (`cat_base_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `meterial_base_categories`
-- ----------------------------
BEGIN;
INSERT INTO `meterial_base_categories` VALUES ('1', 'เครื่องใช้ไฟฟ้า', '4'), ('2', 'เครื่องคอมพิวเตอร์/และอุปกรณ์', '1'), ('3', 'อุปกรณ์สำนักงาน', '2'), ('4', 'สือการเรียนการสอน', '3'), ('5', 'เฟอร์นิเจอร์ลอยตัว', '5'), ('6', 'อื่นๆ', '6');
COMMIT;

-- ----------------------------
--  Table structure for `place`
-- ----------------------------
DROP TABLE IF EXISTS `place`;
CREATE TABLE `place` (
  `place_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'รหัส primary key',
  `place_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ชือ่ห้อง หรือ สถานที่ ',
  `remark` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'หมายเหตุ / รายละเอียด',
  PRIMARY KEY (`place_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `place`
-- ----------------------------
BEGIN;
INSERT INTO `place` VALUES ('1', 'ห้องเรียน 411', null), ('2', 'ห้องพักอาจารย์', null), ('3', 'ห้องเรียน 542', null), ('4', 'ห้องเรียน 553', null), ('5', 'ห้องเรียน 543', null), ('6', 'ห้อง 441', null);
COMMIT;

-- ----------------------------
--  Table structure for `ref_code`
-- ----------------------------
DROP TABLE IF EXISTS `ref_code`;
CREATE TABLE `ref_code` (
  `ref_code` varchar(255) NOT NULL,
  `ref_no` int(11) NOT NULL,
  `detail` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `ref_code`
-- ----------------------------
BEGIN;
INSERT INTO `ref_code` VALUES ('M2', '16', 'เลขที่ วัสดุ & barcode'), ('M1', '14', 'เลขที่ ครุภัณ & barcode'), ('M', '29', 'materail'), ('F', '14', 'รหัสส่งซ่อม'), ('M', '29', 'materail'), ('F', '14', 'รหัสส่งซ่อม');
COMMIT;

-- ----------------------------
--  Table structure for `report_builders`
-- ----------------------------
DROP TABLE IF EXISTS `report_builders`;
CREATE TABLE `report_builders` (
  `rb_id` int(11) NOT NULL AUTO_INCREMENT,
  `sql_text` text,
  `report_name` varchar(255) DEFAULT NULL,
  `webservice_method` varchar(11) DEFAULT NULL,
  `is_xml` int(11) unsigned zerofill DEFAULT NULL,
  `remark` text,
  `param` text,
  PRIMARY KEY (`rb_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
--  Records of `report_builders`
-- ----------------------------
BEGIN;
INSERT INTO `report_builders` VALUES ('10', 'select * from controllers', 'Controllers All', 'controller_', null, '', ''), ('9', 'select * from users', 'All User', 'all_user', null, '', '');
COMMIT;

-- ----------------------------
--  Table structure for `roles`
-- ----------------------------
DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `controller_access` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `roles`
-- ----------------------------
BEGIN;
INSERT INTO `roles` VALUES ('1', 'เจ้าหน้าที่ครุภัณฑ์', '1,2,3,4,5,6,7,8,31,32,33,34,35,36,37,38,39,40,44,45,46,64,65,75,29,30,41,42,49,66,67,68,69,70,72,73,77,78,83,79,80,74,76,84,85,86,87'), ('2', 'ผู้บริหาร', '49,51,52,53,54,60,61,41,42,79,80,84,85,86'), ('3', 'อาจารย์สอน', '65,41,42,66,67,79,80,84,85,86');
COMMIT;

-- ----------------------------
--  Table structure for `sub_allocation_items`
-- ----------------------------
DROP TABLE IF EXISTS `sub_allocation_items`;
CREATE TABLE `sub_allocation_items` (
  `code` varchar(255) CHARACTER SET utf8 NOT NULL,
  `sub_alloc_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `create_date` date NOT NULL,
  `return_date` date NOT NULL,
  `allocation_item_id` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  PRIMARY KEY (`sub_alloc_item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `sub_allocation_items`
-- ----------------------------
BEGIN;
INSERT INTO `sub_allocation_items` VALUES ('บส105/2555', '11', '2012-04-07', '0000-00-00', '17', '1'), ('บส106/2555', '12', '2012-04-07', '0000-00-00', '16', '1');
COMMIT;

-- ----------------------------
--  Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `firstname` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `lastname` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `tel` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `role_id` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ชนิดผู้ใช้งาน',
  `position` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `users`
-- ----------------------------
BEGIN;
INSERT INTO `users` VALUES ('1', 'wirachat', 'e10adc3949ba59abbe56e057f20f883e', 'วิรฉัตร', 'สุขสวัสดิ์', 'wirachat2you@hotmail.com', '0842188408', '1', 'Developer'), ('2', 'staff', 'e10adc3949ba59abbe56e057f20f883e', 'วิรฉัตร', 'สุขสวัสดิ์', 'wirachat2you@hotmail.com', '0842188408', '2', 'Developer'), ('8', 'manager', 'e10adc3949ba59abbe56e057f20f883e', 'อาจารย์นัยนพัศ', 'อินจวงจิรกิตต์', 'itbsru@bsru.ac.th', '08411919191', '2', 'อาจารย์ประจำ'), ('9', 'teacher', 'e10adc3949ba59abbe56e057f20f883e', 'เจษฐา', 'รักไทย', 'aaa@hotmail.com', '08411919191', '3', 'อาจารย์ประจำ');
COMMIT;

-- ----------------------------
--  Table structure for `year_list`
-- ----------------------------
DROP TABLE IF EXISTS `year_list`;
CREATE TABLE `year_list` (
  `year_name` varchar(255) NOT NULL,
  `year_value` varchar(255) NOT NULL,
  PRIMARY KEY (`year_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `year_list`
-- ----------------------------
BEGIN;
INSERT INTO `year_list` VALUES ('2550', '2007'), ('2551', '2008'), ('2552', '2009'), ('2553', '2010'), ('2554', '2011'), ('2555', '2012');
COMMIT;

-- ----------------------------
--  View structure for `allocation_views`
-- ----------------------------
DROP VIEW IF EXISTS `allocation_views`;
CREATE  VIEW `allocation_views` AS select `users`.`firstname` AS `firstname`,`users`.`lastname` AS `lastname`,`allocations`.`allocation_id` AS `allocation_id`,lpad(`allocations`.`allocation_id`,5,'0') AS `code`,date_format(`allocations`.`allocation_date`,'%d/%m/%Y') AS `allocation_date`,`allocations`.`user_id` AS `user_id`,`allocations`.`detail` AS `detail`,`allocations`.`return_date` AS `return_date`,`allocations`.`allocation_type` AS `allocation_type`,(select coalesce(sum(`ai`.`amount`),0) AS `COALESCE(sum(amount),0)` from `allocation_items` `ai` where (`allocations`.`allocation_id` = `ai`.`allocation_id`)) AS `total`,(select (select coalesce(sum(`si`.`amount`),0) AS `COALESCE(sum(si.amount),0)` from `sub_allocation_items` `si` where (`si`.`allocation_item_id` = `ai`.`allocation_item_id`)) AS `(select COALESCE(sum(si.amount),0) from sub_allocation_items si where si.allocation_item_id = ai.allocation_item_id)` from `allocation_items` `ai` where (`allocations`.`allocation_id` = `ai`.`allocation_id`)) AS `allocation_amont` from (`allocations` left join `users` on((`allocations`.`user_id` = `users`.`user_id`))) where 1;

-- ----------------------------
--  View structure for `category_views`
-- ----------------------------
DROP VIEW IF EXISTS `category_views`;
CREATE  VIEW `category_views` AS select `material_categories`.`category_name` AS `category_name`,`material_categories`.`category_id` AS `category_id`,`material_categories`.`cat_base_id` AS `cat_base_id`,`meterial_base_categories`.`cat_base_name` AS `cat_base_name`,`meterial_base_categories`.`sort_order` AS `sort_order` from (`material_categories` join `meterial_base_categories` on((`material_categories`.`cat_base_id` = `meterial_base_categories`.`cat_base_id`)));

-- ----------------------------
--  View structure for `inventory_view`
-- ----------------------------
DROP VIEW IF EXISTS `inventory_view`;
CREATE  VIEW `inventory_view` AS select `material_registers`.`material_id` AS `material_id`,`material_registers`.`name` AS `name`,`material_registers`.`model` AS `model`,`material_registers`.`category_id` AS `category_id`,`material_registers`.`type_id` AS `type_id`,`material_registers`.`buy_date` AS `buy_date`,`material_registers`.`buy_price` AS `buy_price`,`material_registers`.`brand` AS `brand`,`material_registers`.`warranty` AS `warranty`,`material_registers`.`detail` AS `detail`,`material_registers`.`responsible` AS `responsible`,`material_registers`.`thumbnail` AS `thumbnail`,`material_registers`.`create_date` AS `create_date`,`material_registers`.`last_modify` AS `last_modify`,`material_registers`.`budget_id` AS `budget_id`,`material_registers`.`company_id` AS `company_id`,`company`.`company_name` AS `company_name`,`company`.`tel` AS `tel`,`company`.`address` AS `address`,`company`.`fax` AS `fax`,`company`.`email` AS `email`,`material_types`.`type_name` AS `type_name`,`budgets`.`year` AS `year`,`budgets`.`budget_name` AS `budget_name`,(select sum(`material_items`.`amount`) AS `sum(``material_items``.``amount``)` from `material_items` where (`material_items`.`material_id` = `material_registers`.`material_id`)) AS `amout_total` from ((((`material_registers` join `company` on((`material_registers`.`company_id` = `company`.`company_id`))) join `material_categories` on((`material_registers`.`category_id` = `material_categories`.`category_id`))) join `material_types` on((`material_registers`.`type_id` = `material_types`.`type_id`))) join `budgets` on((`material_registers`.`budget_id` = `budgets`.`budget_id`)));

-- ----------------------------
--  View structure for `inventory_views`
-- ----------------------------
DROP VIEW IF EXISTS `inventory_views`;
CREATE  VIEW `inventory_views` AS select lpad(`material_registers`.`material_id`,6,0) AS `code`,`material_registers`.`material_id` AS `material_id`,`material_registers`.`model` AS `model`,`material_registers`.`category_id` AS `category_id`,date_format(`material_registers`.`buy_date`,'%d/%m/%Y') AS `buy_date`,`material_registers`.`buy_price` AS `buy_price`,`material_registers`.`brand` AS `brand`,`material_registers`.`warranty` AS `warranty`,`material_registers`.`detail` AS `detail`,`material_registers`.`responsible` AS `responsible`,`material_registers`.`thumbnail` AS `thumbnail`,`material_registers`.`create_date` AS `create_date`,date_format(`material_registers`.`create_date`,'%d/%m/%Y') AS `create_date2`,`material_registers`.`last_modify` AS `last_modify`,`material_registers`.`budget_id` AS `budget_id`,`material_registers`.`company_id` AS `company_id`,`company`.`tel` AS `tel`,`company`.`fax` AS `fax`,`company`.`email` AS `email`,`material_types`.`type_name` AS `type_name`,`budgets`.`year` AS `year`,`budgets`.`budget_name` AS `budget_name`,(select sum(`material_items`.`amount`) AS `sum(``material_items``.``amount``)` from `material_items` where (`material_items`.`material_id` = `material_registers`.`material_id`)) AS `amount_total`,`material_categories`.`category_name` AS `category_name`,`material_registers`.`name` AS `name`,`company`.`company_name` AS `company_name`,`company`.`address` AS `address`,`material_registers`.`type_id` AS `type_id`,(select coalesce(sum(`im`.`allocation_total`),0) AS `COALESCE(sum(allocation_total ),0)` from `item_amount_views` `im` where (`im`.`material_id` = `material_registers`.`material_id`)) AS `allocation_total` from ((((`material_registers` join `company` on((`material_registers`.`company_id` = `company`.`company_id`))) join `material_categories` on((`material_registers`.`category_id` = `material_categories`.`category_id`))) join `material_types` on((`material_registers`.`type_id` = `material_types`.`type_id`))) join `budgets` on((`material_registers`.`budget_id` = `budgets`.`budget_id`)));

-- ----------------------------
--  View structure for `item_amount_views`
-- ----------------------------
DROP VIEW IF EXISTS `item_amount_views`;
CREATE  VIEW `item_amount_views` AS select `mi`.`code` AS `code`,`mi`.`amount` AS `amount`,`mi`.`material_id` AS `material_id`,(select coalesce(sum(`si`.`amount`),0) AS `COALESCE(sum(amount),0)` from `sub_allocation_items` `si` where (`si`.`code` = `mi`.`code`)) AS `allocation_total` from `material_items` `mi`;

-- ----------------------------
--  View structure for `maintain_views`
-- ----------------------------
DROP VIEW IF EXISTS `maintain_views`;
CREATE  VIEW `maintain_views` AS select `maintains`.`code` AS `code`,`maintains`.`subject` AS `subject`,`maintains`.`remark` AS `remark`,date_format(`maintains`.`create_date`,'%d/%m/%Y') AS `create_date`,`maintains`.`user_id` AS `user_id`,`maintains`.`maintain_id` AS `maintain_id`,lpad(`maintains`.`maintain_id`,4,'0') AS `m_code`,`inventory_view`.`name` AS `name`,`inventory_view`.`model` AS `model`,`inventory_view`.`thumbnail` AS `thumbnail`,`inventory_view`.`buy_date` AS `buy_date`,`inventory_view`.`brand` AS `brand`,`inventory_view`.`detail` AS `detail`,`material_items`.`barcode` AS `barcode`,`users`.`firstname` AS `firstname`,`users`.`lastname` AS `lastname`,date_format(`maintains`.`finish_date`,'%d/%m/%Y') AS `finish_date`,`maintains`.`price` AS `price`,`maintains`.`remark2` AS `remark2`,`maintains`.`warranty` AS `warranty`,`maintains`.`status_id` AS `status_id`,`maintain_status`.`status_name` AS `status_name` from ((((`maintains` join `material_items` on((`maintains`.`code` = `material_items`.`code`))) join `inventory_view` on((`material_items`.`material_id` = `inventory_view`.`material_id`))) left join `users` on((`maintains`.`user_id` = `users`.`user_id`))) join `maintain_status` on((`maintains`.`status_id` = `maintain_status`.`status_id`)));

-- ----------------------------
--  View structure for `material_views`
-- ----------------------------
DROP VIEW IF EXISTS `material_views`;
CREATE  VIEW `material_views` AS select lpad(`material_registers`.`material_id`,6,0) AS `code`,`material_registers`.`material_id` AS `material_id`,`material_registers`.`model` AS `model`,`material_registers`.`category_id` AS `category_id`,date_format(`material_registers`.`buy_date`,'%d/%m/%Y') AS `buy_date`,`material_registers`.`buy_price` AS `buy_price`,`material_registers`.`brand` AS `brand`,`material_registers`.`warranty` AS `warranty`,`material_registers`.`detail` AS `detail`,`material_registers`.`responsible` AS `responsible`,`material_registers`.`thumbnail` AS `thumbnail`,`material_registers`.`create_date` AS `create_date`,date_format(`material_registers`.`create_date`,'%d/%m/%Y') AS `create_date2`,`material_registers`.`last_modify` AS `last_modify`,`material_registers`.`budget_id` AS `budget_id`,`material_registers`.`company_id` AS `company_id`,`company`.`tel` AS `tel`,`company`.`fax` AS `fax`,`company`.`email` AS `email`,`material_types`.`type_name` AS `type_name`,`budgets`.`year` AS `year`,`budgets`.`budget_name` AS `budget_name`,(select sum(`material_items`.`amount`) AS `sum(``material_items``.``amount``)` from `material_items` where (`material_items`.`material_id` = `material_registers`.`material_id`)) AS `amount_total`,`material_categories`.`category_name` AS `category_name`,`material_registers`.`name` AS `name`,`company`.`company_name` AS `company_name`,`company`.`address` AS `address`,`material_registers`.`type_id` AS `type_id`,(select coalesce(sum(`im`.`allocation_total`),0) AS `COALESCE(sum(allocation_total ),0)` from `item_amount_views` `im` where (`im`.`material_id` = `material_registers`.`material_id`)) AS `allocation_total` from ((((`material_registers` join `company` on((`material_registers`.`company_id` = `company`.`company_id`))) join `material_categories` on((`material_registers`.`category_id` = `material_categories`.`category_id`))) join `material_types` on((`material_registers`.`type_id` = `material_types`.`type_id`))) join `budgets` on((`material_registers`.`budget_id` = `budgets`.`budget_id`)));

-- ----------------------------
--  View structure for `material_views2`
-- ----------------------------
DROP VIEW IF EXISTS `material_views2`;
CREATE  VIEW `material_views2` AS select `material_registers`.`material_id` AS `material_id`,`material_registers`.`name` AS `name`,`material_registers`.`model` AS `model`,`material_registers`.`category_id` AS `category_id`,`material_registers`.`type_id` AS `type_id`,`material_registers`.`buy_date` AS `buy_date`,`material_registers`.`buy_price` AS `buy_price`,`material_registers`.`brand` AS `brand`,`material_registers`.`warranty` AS `warranty`,`material_registers`.`detail` AS `detail`,`material_registers`.`responsible` AS `responsible`,`material_registers`.`thumbnail` AS `thumbnail`,`material_registers`.`create_date` AS `create_date`,`material_registers`.`last_modify` AS `last_modify`,`material_registers`.`budget_id` AS `budget_id`,`material_registers`.`company_id` AS `company_id`,`company`.`company_name` AS `company_name`,`company`.`tel` AS `tel`,`company`.`address` AS `address`,`company`.`fax` AS `fax`,`company`.`email` AS `email`,`material_types`.`type_name` AS `type_name`,`budgets`.`year` AS `year`,`budgets`.`budget_name` AS `budget_name` from ((((`material_registers` join `company` on((`material_registers`.`company_id` = `company`.`company_id`))) join `material_categories` on((`material_registers`.`category_id` = `material_categories`.`category_id`))) join `material_types` on((`material_registers`.`type_id` = `material_types`.`type_id`))) join `budgets` on((`material_registers`.`budget_id` = `budgets`.`budget_id`)));

SET FOREIGN_KEY_CHECKS = 1;
